﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;

using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class D_HrmChangeData
    {
        public D_HrmChangeData()
        {

        }
        public DataSet GetChangeData(Nullable<int> Sequence, Nullable<int> EmpSequence, Nullable<int> ChangeSequence, string SearchText)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Sequence);
                dbManager.AddParameters(2, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(3, "@ChangeSequence", ChangeSequence);
                dbManager.AddParameters(4, "@SearchText", SearchText);
                dbManager.AddParameters(5, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT ce.*"
                        + ", req.CHG_REF_NO, req.EMP_SEQ, req.CHG_TYPE_SEQ, req.CHG_DATE, req.CHG_STATUS"
                    + " FROM HRM_CHG_REQUESTS req"
                    + " LEFT JOIN HRM_CHG_EMPLOYEES ce ON req.CHG_REQ_SEQ = ce.CHG_REQ_SEQ"
                    + " WHERE req.RECORD_STATUS <> @DeleteRecord"
                    + " AND ce.RECORD_STATUS <> @DeleteRecord"
                    + (EmpSequence.HasValue ? " AND req.EMP_SEQ = @EmpSequence" : "")
                    + (ChangeSequence.HasValue ? " AND req.CHG_REQ_SEQ = @ChangeSequence" : "")
                    + (Sequence.HasValue ? " AND ce.CHG_EMP_SEQ = @Sequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void Insert(int ChangeSequence, Nullable<int> NamePrefix, string FirstName, string LastName
            , string FirstNameEng, string LastNameEng, string NickName, string Citizen, string TaxPayer
            , string Social, string Official, Nullable<int> Gender, string Religion, string RaceNationalities
            , string Nationalities, string BloodGroup, Nullable<DateTime> BirthDate, string Remarks, int CreatedBy)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(22);

                dbManager.AddParameters(0, "@chg_req_seq", ChangeSequence);
                dbManager.AddParameters(1, "@name_prefix_seq", NamePrefix);
                dbManager.AddParameters(2, "@first_name", FirstName);
                dbManager.AddParameters(3, "@last_name", LastName);
                dbManager.AddParameters(4, "@first_name_eng", FirstNameEng);
                dbManager.AddParameters(5, "@last_name_eng", LastNameEng);
                dbManager.AddParameters(6, "@gender_seq", Gender);
                dbManager.AddParameters(7, "@religion_seq", Religion);
                dbManager.AddParameters(8, "@race_nat_seq", RaceNationalities);
                dbManager.AddParameters(9, "@nat_seq", Nationalities);
                dbManager.AddParameters(10, "@birth_date", BirthDate);
                dbManager.AddParameters(11, "@citizen", Citizen);
                dbManager.AddParameters(12, "@tax_payer", TaxPayer);
                dbManager.AddParameters(13, "@social", Social);
                dbManager.AddParameters(14, "@blood_group", BloodGroup);
                dbManager.AddParameters(17, "@nick_name", NickName);
                dbManager.AddParameters(18, "@remarks", Remarks);
                dbManager.AddParameters(19, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(20, "@created_by", CreatedBy);
                dbManager.AddParameters(21, "@created_when", DateTime.Today);

                strSQL = "INSERT INTO HRM_CHG_EMPLOYEES"
                    + "(CHG_REQ_SEQ, NAME_PREFIX_SEQ, FIRST_NAME, LAST_NAME, FIRST_NAME_ENG, LAST_NAME_ENG"
                        + ", NICK_NAME, GENDER_SEQ, RELIGION_SEQ, RACE_NAT_SEQ, NAT_SEQ, BIRTH_DATE"
                        + ", CITIZEN, TAX_PAYER, SOCIAL, BLOOD_GROUP_SEQ"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES (@chg_req_seq, @name_prefix_seq, @first_name, @last_name, @first_name_eng, @last_name_eng"
                        + ", @nick_name, @gender_seq, @religion_seq, @race_nat_seq, @nat_seq, @birth_date, @citizen"
                        + ", @tax_payer, @social, @blood_group, @remarks, @record_status, @created_by, @created_when"
                        + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(int Sequence, Nullable<int> NamePrefix, string FirstName, string LastName
            , string FirstNameEng, string LastNameEng, string NickName, string Citizen, string TaxPayer
            , string Social, string Official, Nullable<int> Gender, string Religion, string RaceNationalities
            , string Nationalities, string BloodGroup, Nullable<DateTime> BirthDate, string Remarks, int UpdatedBy)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(21);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@name_prefix_seq", NamePrefix);
                dbManager.AddParameters(2, "@first_name", FirstName);
                dbManager.AddParameters(3, "@last_name", LastName);
                dbManager.AddParameters(4, "@first_name_eng", FirstNameEng);
                dbManager.AddParameters(5, "@last_name_eng", LastNameEng);
                dbManager.AddParameters(6, "@gender_seq", Gender);
                dbManager.AddParameters(7, "@religion_seq", Religion);
                dbManager.AddParameters(8, "@race_nat_seq", RaceNationalities);
                dbManager.AddParameters(9, "@nat_seq", Nationalities);
                dbManager.AddParameters(10, "@birth_date", BirthDate);
                dbManager.AddParameters(11, "@citizen", Citizen);
                dbManager.AddParameters(12, "@tax_payer", TaxPayer);
                dbManager.AddParameters(13, "@social", Social);
                dbManager.AddParameters(14, "@blood_group", BloodGroup);
                dbManager.AddParameters(17, "@nick_name", NickName);
                dbManager.AddParameters(18, "@remarks", Remarks);
                dbManager.AddParameters(19, "@updated_by", UpdatedBy);
                dbManager.AddParameters(20, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_CHG_EMPLOYEES"
                    + " SET NAME_PREFIX_SEQ = @name_prefix_seq"
                        + ", FIRST_NAME = @first_name"
                        + ", LAST_NAME = @last_name"
                        + ", FIRST_NAME_ENG = @first_name_eng"
                        + ", LAST_NAME_ENG = @last_name_eng"
                        + ", NICK_NAME = @nick_name"
                        + ", GENDER_SEQ = @gender_seq"
                        + ", RELIGION_SEQ = @religion_seq"
                        + ", RACE_NAT_SEQ = @race_nat_seq"
                        + ", NAT_SEQ = @nat_seq"
                        + ", BIRTH_DATE = @birth_date"
                        + ", CITIZEN = @citizen"
                        + ", TAX_PAYER = @tax_payer"
                        + ", SOCIAL = @social"
                        + ", BLOOD_GROUP_SEQ = @blood_group"
                        + ", REMARKS = @remarks"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE CHG_EMP_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.Close();
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            string strSQL;

            try
            {

                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_CHG_EMPLOYEES"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE CHG_EMP_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.Close();
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            }
        }
    }
}
